/*
	exec sp_billing_report_proc '0','0','0','0','0','0',1,'byMonth','xlreport','6/5/2008:11:45:26'
*/
alter proc sp_billing_report_proc 

@PRODUCTID	VARCHAR(200),
@SONUMBER	VARCHAR(50),
@CUSTOMERID	VARCHAR(200),
@sperson	varchar(50),
@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@ORDERTYPE	INT,
@BYPERIOD	VARCHAR(50),
@REPORTTYPE	VARCHAR(20),
@DEFAULTPROMPT	VARCHAR(50),
@REGION VARCHAR(50),
@ENDUSER VARCHAR(100)
as
begin
	CREATE TABLE #RESULTS 
		( PRODUCTID INT, [REQUESTED QTY] INT, [REQUESTED DATE] DATETIME, [SCHEDULE DATE] DATETIME,
		 [REQUESTED DATE WORK WEEK NO] INT,[REQUESTED DATE QUARTER] INT,
		 [SCHEDULE DATE WORK WEEK NO] INT, [SCHEDULE DATE QUARTER] INT,	 
		 [PRODUCT NAME] VARCHAR(50), [ACTUAL SHIP QTY] INT,[SO NUMBER] VARCHAR(50),
		 [CUSTOMERID] INT,[CUSTOMER PO NUMBER] VARCHAR(50),[SALES PERSON]VARCHAR(50),
		 [SO DATE] DATETIME,[SO WORK WEEK NO] INT,[SO QUARTER] INT,[CUSTOMER NAME] VARCHAR(50),[CUSTOMER NUMBER] VARCHAR(50), 
		 [ACTUAL SHIP DATE] DATETIME, [ACTUAL SHIP DATE WORK WEEK NO] INT,[ACTUAL SHIP DATE QUARTER] INT, 
		 [CONFIRMED DATE] DATETIME, [CONFIRMED DATE WORK WEEK NO] INT,[CONFIRMED DATE QUARTER] INT, 
		 [INVOICE NUMBER] VARCHAR(50),[UNIT PRICE] FLOAT,[EXTENDED PRICE] FLOAT, [END CUSTOMER] VARCHAR(50),
		 [LINE ITEM NUMBER] INT,ISDISTRIBUTOR varchar(50), PERIODICALVALUE INT,ACTUAL_YEAR INT,
		 ISRMACREDIT VARCHAR(20), [END USER] VARCHAR(50), [CREDIT/DEBIT NOTE] VARCHAR(50) )
						
	
	DECLARE @SHIPMENTSTRING NVARCHAR(4000)
	DECLARE @CREADITSTRING	NVARCHAR(4000)
	DECLARE @RMASTRING VARCHAR(4000)

	SET @SHIPMENTSTRING = N'
	INSERT INTO #RESULTS 
	SELECT
		COOL.Product_ID,COOL.COOL_Qty,
		COOL.COOL_Requested_Date,
		ISNULL(COOL.COOL_Schedule_Date, COOL.COOL_Requested_Date),	
		COOL.REQUESTED_DATE_WW_NO,
		REQUESTED_DATE_QTR_NO,
		ISNULL(SCHEDULE_DATE_WW_NO, COOL.REQUESTED_DATE_WW_NO),
		ISNULL(SCHEDULE_DATE_QTR_NO, REQUESTED_DATE_QTR_NO),
		P.Part_Number,CSLIT.CSLIT_Qty_Shipped,SO.SalesOrder_No,SO.Customer_ID,SO.SO_Customer_PO_Number,
		SO_Sales_Person,SO.SO_Booking_Date,SO.SO_BOOKING_DATE_WW_NO,SO.SO_BOOKING_DATE_QTR_NO,
		C1.C_Name, C1.C_Number,CST.CST_Ship_Date,CST_SHIP_DATE_WW_NO, CST_SHIP_DATE_QTR_NO, CST.CST_CONFIRMED_ON, CST_SHIP_DATE_WW_NO, CST_SHIP_DATE_QTR_NO,
		CST.Inv_No,COOL.COOL_Unit_Cost, (COOL.COOL_Unit_Cost * CSLIT.CSLIT_Qty_Shipped ),
		(CASE 	
			WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
 		END),
		COOL.COOL_Line_Item_No,
		(case 	when C1.C_Is_Distributor = 1 then ''Distributor Billing''
			else ''Customer Billing'' 
		end),
		(CASE 
			WHEN '''+ @BYPERIOD +''' = ''BYMONTH'' THEN DATEPART( m, CST.CST_Ship_Date )
			WHEN '''+ @BYPERIOD +''' = ''byQuarter'' THEN CST_SHIP_DATE_QTR_NO
			ELSE DATEPART(yyyy, CST.CST_SHIP_DATE )
		END),DATEPART(yyyy,CST.CST_SHIP_DATE),''NO'',
		(CASE 
			WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
				(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
				 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
			END)
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END),NULL
	FROM
	     ((((Customer_Open_Order_LineItem COOL INNER JOIN  Customer_Shipping_Line_Item_Table CSLIT ON
	        COOL.LineItem_ID = CSLIT.LineItem_ID)
	     INNER JOIN Sales_Order SO ON COOL.SalesOrder_ID = SO.SalesOrder_ID )--and SO.SO_Is_Sample = @order_type
	     INNER JOIN Product P ON COOL.Product_ID = P.Product_ID)
	     INNER JOIN Customer_Shipping_Table CST ON  CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID)
	     INNER JOIN Customer C1 ON SO.Customer_ID = C1.Customer_ID	     
	WHERE
	    CST.CST_Status = ''SHIPPED'' and so_is_consignment = 1  '

	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @SONUMBER IS NOT NULL AND @SONUMBER <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND SO.SalesOrder_No = '''+ @SONUMBER+''''
	END
	IF @ORDERTYPE IS NOT NULL AND len(@ORDERTYPE) > 0
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING +' AND CST.CST_SHIP_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + +' AND CST.CST_SHIP_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	IF @REGION IS NOT NULL AND @REGION <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND ISNULL( ISNULL( (SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ),(SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ) ), C1.C_Region ) = '''+ @REGION+''''
	END
    IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
	BEGIN
		SET @SHIPMENTSTRING = @SHIPMENTSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
								END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END
	--print @SHIPMENTSTRING
	EXEC (@SHIPMENTSTRING)

	SET @CREADITSTRING = N' INSERT INTO #RESULTS 
	([SO NUMBER],[ACTUAL SHIP DATE],[UNIT PRICE],[EXTENDED PRICE],[ACTUAL SHIP QTY],
	[CUSTOMER PO NUMBER],[SO DATE],[SO WORK WEEK NO],[SO QUARTER],
	[SALES PERSON],[CUSTOMERID],[CUSTOMER NAME],[CUSTOMER NUMBER],
	[END CUSTOMER],[END USER],
	--[ACTUAL SHIP DATE],[CONFIRMED DATE WORK WEEK NO],[CONFIRMED DATE QUARTER],
	[INVOICE NUMBER],[LINE ITEM NUMBER],[REQUESTED QTY],[REQUESTED DATE],[SCHEDULE DATE],
	[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER],[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER],
	PRODUCTID,[PRODUCT NAME],PERIODICALVALUE,ACTUAL_YEAR,ISDISTRIBUTOR,[CREDIT/DEBIT NOTE] )

	SELECT 
		CREDITNOTE_NO,ISSUE_DATE,
		(CASE WHEN IS_CREDIT_NOTE = 0 THEN DISCOUNT_PER_UNIT
			ELSE (DISCOUNT_PER_UNIT * -1)
		END),
		(CASE WHEN IS_CREDIT_NOTE = 0 THEN DISCOUNT_AMOUNT
			ELSE (DISCOUNT_AMOUNT * -1)
		END),
		(CASE WHEN IS_CREDIT_NOTE = 0 THEN ADJUSTMENT_QTY
			ELSE (ADJUSTMENT_QTY * -1)
		END),
		SO_CUSTOMER_PO_NUMBER,SO_Booking_Date,SO.SO_BOOKING_DATE_WW_NO,SO.SO_BOOKING_DATE_QTR_NO,
		SO_Sales_Person,C.CUSTOMER_ID,C_NAME,C_NUMBER,
		(CASE 	
			WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
 		END),
		(CASE 
			WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
				(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
				 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
			END)
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END),
		--CST_SHIP_DATE,CST_SHIP_DATE_WW_NO,CST_SHIP_DATE_QTR_NO,
		INV_NO,COOL_LINE_ITEM_NO,COOL_QTY,
		COOL_REQUESTED_DATE,
		ISNULL(COOL_SCHEDULE_DATE, COOL_REQUESTED_DATE),
		COOL.REQUESTED_DATE_WW_NO,
		REQUESTED_DATE_QTR_NO,
		ISNULL(SCHEDULE_DATE_WW_NO, COOL.REQUESTED_DATE_WW_NO),
		ISNULL(SCHEDULE_DATE_QTR_NO, REQUESTED_DATE_QTR_NO),
		P.PRODUCT_ID,PART_NUMBER,
		(CASE 
			WHEN '''+ @BYPERIOD +''' = ''BYMONTH'' THEN DATEPART( m, CST_SHIP_DATE )
			WHEN '''+ @BYPERIOD +''' = ''byQuarter'' THEN DATEPART( q, CST_SHIP_DATE )
			ELSE DATEPART(yyyy, CST_SHIP_DATE )
		END),DATEPART(yyyy,CST_SHIP_DATE),
		(case 	when C.C_Is_Distributor = 1 then ''Distributor Billing''
			else ''Customer Billing'' 
		end),
		(CASE WHEN IS_CREDIT_NOTE = 0 THEN ''CREDIT NOTES''
			ELSE ''DEBIT NOTES''
		END)
	FROM
		CREDIT_NOTE_TABLE CNT INNER JOIN CREDIT_NOTE_LINE_ITEM_TABLE CNLIT ON CNT.CREDITNOTE_ID = CNLIT.CN_ID 
		INNER JOIN SALES_ORDER SO ON CNT.SALESORDER_ID = SO.SALESORDER_ID
		INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID
		INNER JOIN CUSTOMER_SHIPPING_TABLE CST ON CNT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID
		LEFT OUTER JOIN CUSTOMER_OPEN_ORDER_LINEITEM COOL ON CNLIT.LINEITEM_ID = COOL.LINEITEM_ID 
		INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID  
	WHERE 	
		1=1 '
	
	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @SONUMBER IS NOT NULL AND @SONUMBER <> '0'
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND CREDITNOTE_NO = '''+ @SONUMBER+''''
	END
	IF @ORDERTYPE IS NOT NULL AND len(@ORDERTYPE) > 0
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING +' AND ISSUE_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + +' AND ISSUE_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
	BEGIN
		SET @CREADITSTRING = @CREADITSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
								END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END
	--print @CREADITSTRING
	EXEC (@CREADITSTRING)

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN
		select 
			[CUSTOMER NAME],[END CUSTOMER],[END USER],[SO NUMBER],[LINE ITEM NUMBER],[CUSTOMER PO NUMBER],[SALES PERSON],
			[SO DATE],[SO WORK WEEK NO],[SO QUARTER],
			[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER],
			[SCHEDULE DATE],[SCHEDULE DATE WORK WEEK NO], [SCHEDULE DATE QUARTER],
		 	[ACTUAL SHIP DATE],[ACTUAL SHIP DATE WORK WEEK NO],[ACTUAL SHIP DATE QUARTER], 
			DATEPART( m, [ACTUAL SHIP DATE]) [ACTUAL SHIP DATE MONTH], DATEPART(yyyy, [ACTUAL SHIP DATE]) [ACTUAL SHIP DATE YEAR],
		 	[CONFIRMED DATE],[CONFIRMED DATE WORK WEEK NO],[CONFIRMED DATE QUARTER],
			[PRODUCT NAME],[REQUESTED QTY],[ACTUAL SHIP QTY],[UNIT PRICE],[EXTENDED PRICE],[INVOICE NUMBER]--,[CREDIT/DEBIT NOTE]
		from #results
		order by ACTUAL_YEAR,ISDISTRIBUTOR,[CREDIT/DEBIT NOTE],PERIODICALVALUE,[ACTUAL SHIP DATE] 
	END
	ELSE
	BEGIN
		SELECT 		
			PRODUCTID, [REQUESTED QTY], [REQUESTED DATE], [SCHEDULE DATE], 
			[PRODUCT NAME], [ACTUAL SHIP QTY],[SO NUMBER], [CUSTOMERID], 
			[CUSTOMER PO NUMBER],[SO DATE], [CUSTOMER NAME],
			[CUSTOMER NUMBER], [ACTUAL SHIP DATE],[SALES PERSON],[INVOICE NUMBER],
			[UNIT PRICE],[EXTENDED PRICE], [END CUSTOMER],[LINE ITEM NUMBER],
			ISDISTRIBUTOR, PERIODICALVALUE,ACTUAL_YEAR,ISRMACREDIT, [END USER],[CREDIT/DEBIT NOTE]
		FROM #results
		order by ACTUAL_YEAR,PERIODICALVALUE,[ACTUAL SHIP DATE] 
	END
end